﻿var Excel = require('exceljs');
var mongoose = require('mongoose');

mongoose.connect("mongodb://localhost:27017/mern-starter");
var Mark = require('./mark_model.js').grade;
var Student = require('../mongo_student.js').students;
var exam_id= "001";


var workbook = new Excel.stream.xlsx.WorkbookWriter({
  filename: './'+ exam_id + '_grades.xlsx'
});
var worksheet = workbook.addWorksheet('Sheet');

worksheet.columns = [
  { header: 'id', key: 'id' },
  { header: 'name', key: 'name' },
  { header: 'class', key: 'class' },
  { header: 'mark', key: 'mark' }
];

//查询数据库，exam_id=spec
Mark.find({"exam_id":exam_id}, {grades:1, _id:0}, function(err,docs){
	var stuID_arr = new Array();
	var stuID_grade_list = new Array();
	for(let i in docs){
		var grade_group_str = docs[i].grades;
		var grade_group = grade_group_str.split('/');
		
		for(let j in grade_group){ 
			stuID_arr.push(grade_group[j].split(':')[0]);
			
			var grade_one = new Object();
			grade_one.id = grade_group[j].split(':')[0];
			grade_one.mark = grade_group[j].split(':')[1];
			stuID_grade_list.push(grade_one);
		}
	}
		
	//查询姓名和班级
	Student.find({"studentNo":{"$in":stuID_arr}}, {classNo:1, name:1, studentNo:1, _id:0}, function(err,docs){
		//console.log(docs);
		var grade_arr = new Array();
				
		if(docs.length > 0){
			for(let j in docs){
				for(let z in stuID_grade_list){
					if(stuID_grade_list[z].id == docs[j].studentNo){
						var grade_one = new Object();
						grade_one.id = stuID_grade_list[z].id;
						grade_one.mark = stuID_grade_list[z].mark;
						grade_one.name = docs[j].name;
						grade_one.class = docs[j].classNo;
								
						grade_arr.push(grade_one);
					}
				}
			}
					
			//console.log(grade_arr);
			//to excel
			for(let i in grade_arr) {
  				worksheet.addRow(grade_arr[i]).commit();
  			}
  			console.log('添加数据完毕：');
			workbook.commit();
		}
	});
})
	


/*
var start_time = new Date();
var data = [{
  id: 2014213111,
  name: '张三三',
  class: '2014215177',
  mark: '90'
  
},
{
  id: 2014213112,
  name: '李四四',
  class: '2014215178',
  mark: '66'
}
];
var length = data.length;

// 当前进度
var current_num = 0;
var time_monit = 400;
var temp_time = Date.now();

console.log('开始添加数据');
// 开始添加数据
for(let i in data) {
  worksheet.addRow(data[i]).commit();
  current_num = i;
  if(Date.now() - temp_time > time_monit) {
    temp_time = Date.now();
    console.log((current_num / length * 100).toFixed(2) + '%');
  }
}
console.log('添加数据完毕：', (Date.now() - start_time));
workbook.commit();

var end_time = new Date();
var duration = end_time - start_time;

console.log('用时：' + duration);
console.log("程序执行完毕");
*/
